﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using model;
using System.Data;

namespace Dal
{
    public class ApplicationDal
    {
        public List<Application> GetList(string eno)
        {
            string sql = "select * from Application where eno='" + eno + "'";
            DataSet ds = DbSqlHelper.Query(sql);
            List<Application> apps = new List<Application>();
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                Application app = new Application();
                app.Ano = Convert.ToInt32(row["ano"]);
                app.Position = row["position"].ToString();
                app.Number = row["number"].ToString();
                app.Salary = row["salary"].ToString();
                apps.Add(app);
            }
            return apps;
        }
        public Application GetSingle(int ano)
        {
            string sql = "select * from Application where ano="+ano;
            DataSet ds = DbSqlHelper.Query(sql);
            Application app = new Application();
            DataRow row = ds.Tables[0].Rows[0];
            app.Position = row["position"].ToString();
            app.Place = row["place"].ToString();
            app.Number = row["number"].ToString();
            app.Salary = row["salary"].ToString();
            app.Conn = row["conn"].ToString();
            app.Aintro = row["aintro"].ToString();
            app.Time = Convert.ToDateTime(row["time"]);
            app.Eno = row["eno"].ToString();
            return app;
        }
        public void Update(Application app)
        {
            string sql = string.Format("update Application set position='{0}',place='{1}',number='{2}',salary='{3}',conn='{4}',aintro='{5}' where ano='{6}'",app.Position,app.Place,app.Number,app.Salary,app.Conn,app.Aintro,app.Ano);
            DbSqlHelper.ExecuteSql(sql);
        }
        public void Delete(int ano)
        {
            string sql = "delete from Application where ano=" + ano;
            DbSqlHelper.ExecuteSql(sql);
        }
        public void Insert(Application app)
        {
            string sql = string.Format("insert into Application (position,place,number,salary,conn,aintro,eno)values('{0}','{1}','{2}','{3}','{4}','{5}','{6}')",app.Position,app.Place,app.Number,app.Salary,app.Conn,app.Aintro,app.Eno);
            DbSqlHelper.ExecuteSql(sql);
        }
        public void Delete(string eno)
        {
            string sql = "delete from Application where eno='" + eno + "'";
            DbSqlHelper.ExecuteSql(sql);
        }
       /* public DataTable getApplications(string keyword, int i)
        {
            string sql = "select * from Application";
            if (!string.IsNullOrEmpty(keyword))
            {
                sql += " where eno like '%" + keyword + "%' or position like '%" + keyword + "%' or place like '%" + keyword + "%'";
            }
            return DbSqlHelper.Query(sql).Tables[0];
        }*/
        public DataTable getApplications(string keyword, int i)
        {
            string sql = "select * from Application a,Enterprise e where a.eno=e.eno";
            if (!string.IsNullOrEmpty(keyword))
            {
                sql += " and (a.eno like '%" + keyword + "%' or a.position like '%" + keyword + "%' or a.place like '%" + keyword + "%' or e.ename like '%" + keyword + "%')";
            }
            return DbSqlHelper.Query(sql).Tables[0];
        }
    }
}
